﻿using System;
using System.Collections.Generic;
using Shuba.Model;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;

namespace Shuba.Data
{
    public class AlternateUM
    {
        public AlternateUM()
        {
        }

        public List<AlternateUMInfo> GetList(string value)
        {
            List<AlternateUMInfo> list = new List<AlternateUMInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT DISTINCT AU.BRANCH_ID, ISNULL(B.BRANCH_NAME, '') BRANCH_NAME, AU.NIP, ISNULL(VU.USER_NAME, '') USER_NAME, AU.ALTERNATE_FOR, AU.EFF_DATE_FROM,
                                   AU.EFF_DATE_TO, AU.ENTRY_BY, AU.ENTRY_DATE, AU.UPDATE_BY, AU.UPDATE_DATE, AU.IS_ACTIVE
                                FROM TBL_ALTERNATE_UM AU
                                LEFT OUTER JOIN V_USER VU ON AU.NIP = VU.NIP
                                LEFT OUTER JOIN TBL_MS_BRANCH B ON AU.BRANCH_ID = B.BRANCH_ID
                                WHERE 
                                    (AU.NIP LIKE '' + @PARAM + '' OR B.BRANCH_NAME LIKE '%' + @PARAM + '%' OR AU.BRANCH_ID LIKE '%' + @PARAM + '%' ) 
                                ORDER BY BRANCH_NAME
                               ");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11)));
                }
                dr.Close();
            }
            return list;
        }

        public AlternateUMInfo Get(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
                                FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') AND IS_ACTIVE = '1'"); //21 sept 11 tambahan is active = 1
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        public AlternateUMInfo GetUnionTmp(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
                                FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') AND IS_ACTIVE = '1'
                                UNION 
                                SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
                                FROM TBL_ALTERNATE_UM_TMP WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') AND IS_ACTIVE = '1'"); //21 sept 11 tambahan is active = 1
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        

        public AlternateUMInfo Get(string BRANCH_ID, string NIP, string ALTERNATE_FOR, DateTime EFF_FROM, DateTime EFF_TO)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
                                FROM TBL_ALTERNATE_UM 
                                WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') 
                                    AND (ALTERNATE_FOR LIKE '' + @ALTERNATE_FOR + '')
                                    AND (CONVERT(VARCHAR(10), EFF_DATE_FROM, 120) = CONVERT(VARCHAR(10), @EFF_DATE_FROM, 120))
                                    AND (CONVERT(VARCHAR(10), EFF_DATE_TO, 120) = CONVERT(VARCHAR(10), @EFF_DATE_TO, 120))
                                    AND IS_ACTIVE = '1'"); //21 sept 11 tambahan is active = 1
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, ALTERNATE_FOR);
            db.AddInParameter(cmd, "@EFF_DATE_FROM", DbType.DateTime, EFF_FROM);
            db.AddInParameter(cmd, "@EFF_DATE_TO", DbType.DateTime, EFF_TO);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        public AlternateUMInfo GetExistingAlternateUM(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP <> '' + @NIP + '') AND IS_ACTIVE = '1'
            UNION 
            SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM_TMP WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP <> '' + @NIP + '') AND IS_ACTIVE = '1'");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        public AlternateUMInfo GetAlternateUMNotActive(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '')");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        public List<AlternateUMInfo> GetAlternateUMNew(string BRANCH_ID, string NIP)
        {
            List<AlternateUMInfo> ri = new List<AlternateUMInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') GROUP BY BRANCH_ID,NIP, IS_ACTIVE ORDER BY BRANCH_ID,NIP,IS_ACTIVE");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri.Add(new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2)));
                }
                dr.Close();
            }
            return ri;
        }

        

        public AlternateUMInfo GetTmp(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM_TMP WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '')");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

        public AlternateUMInfo GetAlternateUMNotExists(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
            FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '')AND IS_ACTIVE = 0 AND NOT EXISTS(SELECT * FROM TBL_ALTERNATE_UM WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '') AND IS_ACTIVE = 1)");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
                }
                dr.Close();
            }
            return ri;
        }

//        public AlternateUMInfo GetTmp(string BRANCH_ID, string NIP)
//        {
//            AlternateUMInfo ri = new AlternateUMInfo();
//            Database db = DBConnection.CreateDatabase();
//            DbCommand cmd = db.GetSqlStringCommand(@"
//                                SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, IS_ACTIVE 
//                                FROM TBL_ALTERNATE_UM_TMP WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '')"); //21 sept 11 tambahan is active = 1
//            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
//            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
//            using (IDataReader dr = db.ExecuteReader(cmd))
//            {
//                while (dr.Read())
//                {
//                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9));
//                }
//                dr.Close();
//            }
//            return ri;
//        }

        public List<AlternateUMInfo> GetAlternateUM(string BRANCH_ID)
        {
            List<AlternateUMInfo> ri = new List<AlternateUMInfo>();
            Database db = DBConnection.CreateDatabase();            
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT [BRANCH_ID],[NIP],[ALTERNATE_FOR],[EFF_DATE_FROM],[EFF_DATE_TO],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_ACTIVE] FROM TBL_ALTERNATE_UM WHERE BRANCH_ID =@BRANCH_ID AND ALTERNATE_FOR = 'UM' AND IS_ACTIVE = '1'
                                                     UNION        
                                                     SELECT [BRANCH_ID],[NIP],[ALTERNATE_FOR],[EFF_DATE_FROM],[EFF_DATE_TO],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_ACTIVE] FROM TBL_ALTERNATE_UM_TMP WHERE BRANCH_ID =@BRANCH_ID AND ALTERNATE_FOR = 'UM' AND IS_ACTIVE = '1'");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri.Add(new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9)));
                }
                dr.Close();
            }
            return ri;
        }

        public AlternateUMInfo GetApprovalByID(string BRANCH_ID, string NIP)
        {
            AlternateUMInfo ri = new AlternateUMInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT BRANCH_ID, NIP, ALTERNATE_FOR, EFF_DATE_FROM, EFF_DATE_TO, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE, IS_ACTIVE, BRANCH_ID_ORIG, NIP_ORIG 
                                FROM TBL_ALTERNATE_UM_TMP WHERE (BRANCH_ID = '' + @BRANCH_ID + '') AND (NIP = '' + @NIP + '')");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ri = new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetDateTime(4), dr.GetString(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetString(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return ri;
        }

        public List<AlternateUMInfo> GetListApproval(string UpdateBy)
        {
            //07 November 2013
            //Imam Pradana
            //
            //Penambahan ISNULL karena terjadi error ketika ada date NULL

            List<AlternateUMInfo> list = new List<AlternateUMInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @" SELECT DISTINCT 
	                        ISNULL(AU.BRANCH_ID, '') BRANCH_ID, 
	                        ISNULL(B.BRANCH_NAME, '') BRANCH_NAME, 
	                        ISNULL(AU.NIP, '') NIP, 
	                        ISNULL(VU.USER_NAME,'') USER_NAME, 
	                        ISNULL(AU.ALTERNATE_FOR, '') ALTERNATE_FOR, 
	                        ISNULL(AU.EFF_DATE_FROM, '') EFF_DATE_FROM, 
	                        ISNULL(AU.EFF_DATE_TO, '') EFF_DATE_TO, 
                            AU.ENTRY_BY, 
                            AU.ENTRY_DATE, 
                            AU.UPDATE_BY, 
                            AU.UPDATE_DATE, 
                            AU.ACT_TYPE, 
                            AU.IS_ACTIVE, 
                            AU.BRANCH_ID_ORIG, 
                            AU.NIP_ORIG
                        FROM TBL_ALTERNATE_UM_TMP AU
                            LEFT OUTER JOIN V_USER VU ON AU.NIP = VU.NIP
                            LEFT OUTER JOIN TBL_MS_BRANCH B ON AU.BRANCH_ID = B.BRANCH_ID
                            WHERE (RTRIM(AU.UPDATE_BY) <> '' + @UPDATE_BY + '') 
                            ORDER BY BRANCH_NAME ";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new AlternateUMInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetDateTime(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11), dr.GetString(12), dr.GetString(13), dr.GetString(14)));
                }
                dr.Close();
            }
            return list;
        }

        public int Save(AlternateUMInfo AlternateUMInfo)
        {
            int x = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql = @"UPDATE [TBL_ALTERNATE_UM_TMP] 
                           SET 
                              [ALTERNATE_FOR] = @ALTERNATE_FOR
                              ,[EFF_DATE_FROM] = @EFF_DATE_FROM
                              ,[EFF_DATE_TO] = @EFF_DATE_TO
                              ,[ENTRY_BY] = @ENTRY_BY
                              ,[ENTRY_DATE] = @ENTRY_DATE
                              ,[UPDATE_BY] = @UPDATE_BY
                              ,[UPDATE_DATE] = @UPDATE_DATE
                              ,[ACT_TYPE] = @ACT_TYPE
                              ,[IS_ACTIVE] = @IS_ACTIVE
                              ,[BRANCH_ID_ORIG] = @BRANCH_ID_ORIG
                              ,[NIP_ORIG] = @NIP_ORIG
                              ,[ALTERNATE_FOR_ORIG] = @ALTERNATE_FOR_ORIG
                              ,[EFF_DATE_FROM_ORIG] = @EFF_DATE_FROM_ORIG
                              ,[EFF_DATE_TO_ORIG] = @EFF_DATE_TO_ORIG
                        WHERE (BRANCH_ID = '' + LEFT(@BRANCH_ID,6) + '') 
                              AND (NIP = '' + LEFT(@NIP,9) + '') 
                              AND (ALTERNATE_FOR = '' + LEFT(@ALTERNATE_FOR ,10) + '') 
                              AND (CONVERT(VARCHAR(10), EFF_DATE_FROM ,120) = CONVERT(VARCHAR(10), @EFF_DATE_FROM ,120))
                              AND (CONVERT(VARCHAR(10), EFF_DATE_TO ,120) = CONVERT(VARCHAR(10), @EFF_DATE_TO ,120));
                            IF @@ROWCOUNT = 0
                        INSERT INTO [TBL_ALTERNATE_UM_TMP] 
                                    ([BRANCH_ID],[NIP],[ALTERNATE_FOR],
                                    [EFF_DATE_FROM],[EFF_DATE_TO],[ENTRY_BY],
                                    [ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],
                                    [ACT_TYPE],[IS_ACTIVE],[BRANCH_ID_ORIG],[NIP_ORIG],[ALTERNATE_FOR_ORIG],[EFF_DATE_FROM_ORIG],[EFF_DATE_TO_ORIG]) 
                        VALUES (LEFT(@BRANCH_ID,6), LEFT(@NIP,9), @ALTERNATE_FOR,
                                @EFF_DATE_FROM, @EFF_DATE_TO, @ENTRY_BY, 
                                @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE,
                                @IS_ACTIVE, @BRANCH_ID_ORIG, @NIP_ORIG,@ALTERNATE_FOR_ORIG,@EFF_DATE_FROM_ORIG,@EFF_DATE_TO_ORIG)";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, AlternateUMInfo.BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, AlternateUMInfo.NIP);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, AlternateUMInfo.ALTERNATE_FOR);
                db.AddInParameter(cmd, "@EFF_DATE_FROM", DbType.DateTime, AlternateUMInfo.EFF_DATE_FROM);
                db.AddInParameter(cmd, "@EFF_DATE_TO", DbType.DateTime, AlternateUMInfo.EFF_DATE_TO);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, AlternateUMInfo.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, AlternateUMInfo.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, AlternateUMInfo.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, AlternateUMInfo.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, AlternateUMInfo.ACT_TYPE);
                db.AddInParameter(cmd, "@IS_ACTIVE", DbType.String, AlternateUMInfo.IS_ACTIVE);
                db.AddInParameter(cmd, "@BRANCH_ID_ORIG", DbType.String, AlternateUMInfo.BRANCH_ID_ORIG);
                db.AddInParameter(cmd, "@NIP_ORIG", DbType.String, AlternateUMInfo.NIP_ORIG);
                db.AddInParameter(cmd, "@ALTERNATE_FOR_ORIG", DbType.String, AlternateUMInfo.ALTERNATE_FOR_ORIG);
                db.AddInParameter(cmd, "@EFF_DATE_FROM_ORIG", DbType.DateTime, AlternateUMInfo.EFF_DATE_FROM_ORIG);
                db.AddInParameter(cmd, "@EFF_DATE_TO_ORIG", DbType.DateTime, AlternateUMInfo.EFF_DATE_TO_ORIG);
                x = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { }
            return x;
        }

        /*
        public int Save(AlternateUMInfo AlternateUMInfo)
        {
            int x = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql = @"UPDATE [TBL_ALTERNATE_UM_TMP] 
                           SET 
                              [ALTERNATE_FOR] = @ALTERNATE_FOR
                              ,[EFF_DATE_FROM] = @EFF_DATE_FROM
                              ,[EFF_DATE_TO] = @EFF_DATE_TO
                              ,[ENTRY_BY] = @ENTRY_BY
                              ,[ENTRY_DATE] = @ENTRY_DATE
                              ,[UPDATE_BY] = @UPDATE_BY
                              ,[UPDATE_DATE] = @UPDATE_DATE
                              ,[ACT_TYPE] = @ACT_TYPE
                              ,[IS_ACTIVE] = @IS_ACTIVE
                              ,[BRANCH_ID_ORIG] = @BRANCH_ID_ORIG
                              ,[NIP_ORIG] = @NIP_ORIG
                        WHERE (BRANCH_ID LIKE '' + LEFT(@BRANCH_ID,6) + '') AND (NIP LIKE '' + LEFT(@NIP,9) + '') ;
                            IF @@ROWCOUNT = 0
                        INSERT INTO [TBL_ALTERNATE_UM_TMP] 
                                    ([BRANCH_ID],[NIP],[ALTERNATE_FOR],
                                    [EFF_DATE_FROM],[EFF_DATE_TO],[ENTRY_BY],
                                    [ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],
                                    [ACT_TYPE],[IS_ACTIVE],[BRANCH_ID_ORIG],[NIP_ORIG]) 
                        VALUES (LEFT(@BRANCH_ID,6), LEFT(@NIP,9), @ALTERNATE_FOR,
                                @EFF_DATE_FROM, @EFF_DATE_TO, @ENTRY_BY, 
                                @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE,
                                @IS_ACTIVE, @BRANCH_ID_ORIG, @NIP_ORIG)";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, AlternateUMInfo.BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, AlternateUMInfo.NIP);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, AlternateUMInfo.ALTERNATE_FOR);
                db.AddInParameter(cmd, "@EFF_DATE_FROM", DbType.DateTime, AlternateUMInfo.EFF_DATE_FROM);
                db.AddInParameter(cmd, "@EFF_DATE_TO", DbType.DateTime, AlternateUMInfo.EFF_DATE_TO);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, AlternateUMInfo.ENTRY_BY);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, AlternateUMInfo.ENTRY_DATE);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, AlternateUMInfo.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, AlternateUMInfo.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, AlternateUMInfo.ACT_TYPE);
                db.AddInParameter(cmd, "@IS_ACTIVE", DbType.String, AlternateUMInfo.IS_ACTIVE);
                db.AddInParameter(cmd, "@BRANCH_ID_ORIG", DbType.String, AlternateUMInfo.BRANCH_ID_ORIG);
                db.AddInParameter(cmd, "@NIP_ORIG", DbType.String, AlternateUMInfo.NIP_ORIG);
                x = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { }
            return x;
        }
        */

        public int Approve(string BRANCH_ID, string NIP, string ALTERNATE_FOR, DateTime EFF_DATE_FROM, DateTime EFF_DATE_TO, string ActType)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("APPROVAL_ALTERNATE_UM");

                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, ALTERNATE_FOR);
                db.AddInParameter(cmd, "@EFF_DATE_FROM", DbType.DateTime, EFF_DATE_FROM);
                db.AddInParameter(cmd, "@EFF_DATE_TO", DbType.DateTime, EFF_DATE_TO);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        /*
        public int Approve(string BRANCH_ID, string NIP, string ActType)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("APPROVAL_ALTERNATE_UM");

                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }
        */

        public int Reject(string BRANCH_ID, string NIP, string ALTERNATE_FOR, DateTime EFF_DATE_FROM, DateTime EFF_DATE_TO)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql;
                DbCommand cmd;

                sql = @"DELETE FROM TBL_ALTERNATE_UM_TMP 
                        WHERE BRANCH_ID = @BRANCH_ID 
                            AND NIP = @NIP 
                            AND ALTERNATE_FOR = @ALTERNATE_FOR 
                            AND CONVERT(VARCHAR(10),EFF_DATE_FROM,120) = CONVERT(VARCHAR(10),@EFF_DATE_FROM,120) 
                            AND CONVERT(VARCHAR(10),EFF_DATE_TO,120) = CONVERT(VARCHAR(10),@EFF_DATE_TO,120)";
                cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, ALTERNATE_FOR);
                db.AddInParameter(cmd, "@EFF_DATE_FROM", DbType.DateTime, EFF_DATE_FROM);
                db.AddInParameter(cmd, "@EFF_DATE_TO", DbType.DateTime, EFF_DATE_TO);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }

        /*
        public int Reject(string BRANCH_ID, string NIP)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql;
                DbCommand cmd;

                sql = String.Format("DELETE FROM TBL_ALTERNATE_UM_TMP WHERE BRANCH_ID LIKE '{0}' AND NIP LIKE '{1}'", BRANCH_ID, NIP);
                cmd = db.GetSqlStringCommand(sql);

                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return status;
        }
         */

        public bool CheckMapping(string BRANCH_ID, string NIP)
        {
            bool isExist = false;

            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetSqlStringCommand(@"SELECT * FROM TBL_MS_BRANCH_NIP_SO WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP");
            mydb.AddInParameter(mycomm, "@BRANCH_ID", DbType.String, BRANCH_ID);
            mydb.AddInParameter(mycomm, "@NIP", DbType.String, NIP);
            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    isExist = true;
                }
                mydr.Close();
            }
            return isExist;
        }
    }
}